﻿

CREATE proc [CashSale].[SaveCashSale]

@CashSaleID			int = 0, 
@InvoiceNo			int, 
@SaleDate			datetime2(7), 
@OrgKey				int = null, 
@Details			dbo.udtCashSaleDetails readonly,
@UserID				int,
@PaymentMode		int,
@OrderDate			datetime2(7),
@OrderDetail		nvarchar(50) = null,
@DeliverName		nvarchar(50) = null, 
@DeliverName2		nvarchar(50) = null, 
@DeliverOrt			nvarchar(50) = null, 
@DeliverPlz			nvarchar(50) = null, 
@DeliverStrasse		nvarchar(50) = null,
@UseDeliveryAddress bit = 0,
@JobDetails			dbo.udtJobs readonly,
@WriteToLiquiDate	datetime2(7) = null,
@WriteInvoiceToBvDate datetime2(7) = null,
@WriteInvoiceToVrspDate datetime2(7) = null

as
begin
	
	set nocount on

	declare @RechtsFKey int,
			@rc int,
			@now datetime2(7) = sysdatetime(),
			@logText nvarchar(150),
			@saveDeliveryAddress tinyint = 3

	if not exists(select * from @Details)
	begin
		Select @CashSaleID 'CashSaleID', -1 'Return', 'Save without @Details is not possible' 'Error' 
		goto WithError
	end

	if not exists(select * from @JobDetails)
	begin
		Select @CashSaleID 'CashSaleID', -1 'Return', 'Save without @jobDetails is not possible' 'Error' 
		goto WithError
	end

	begin tran

	begin try

		--======================================    
		;with sel
		as
		(
			select	@CashSaleID					'CashSaleID',
					@InvoiceNo					'InvoiceNo',
					@SaleDate					'SaleDate',
					@OrgKey						'OrgKey',
					@UserID						'UserID',
					@PaymentMode				'PaymentMode',
					@OrderDate					'OrderDate',
					@OrderDetail				'OrderDetail',
					@UseDeliveryAddress			'UseDeliveryAddress',
					@WriteToLiquiDate			'WriteToLiquiDate',
					@WriteInvoiceToBvDate		'WriteInvoiceToBvDate',
					@WriteInvoiceToVrspDate		'WriteInvoiceToVrspDate'
		)
		merge into [CashSale].[datCashSale] t
		using sel as s on
			s.CashSaleID = t.CashSaleID
		when not matched by target then
		insert (
				InvoiceNo, 
				SaleDate, 
				OrgKey, 
				UserID,
				PaymentMode,
				OrderDate,
				OrderDetail,
				UseDeliveryAddress,
				WriteToLiquiDate,
				WriteInvoiceToBvDate,
				WriteInvoiceToVrspDate
				)
		values (
				s.InvoiceNo, 
				s.SaleDate, 
				s.OrgKey, 
				s.UserID,
				s.PaymentMode,
				s.OrderDate,
				s.OrderDetail,
				s.UseDeliveryAddress,
				s.WriteToLiquiDate,
				s.WriteInvoiceToBvDate,
				s.WriteInvoiceToVrspDate
				)
		when matched then
		update 
			set	
				InvoiceNo				= s.InvoiceNo, 
				SaleDate				= s.SaleDate, 
				OrgKey					= s.OrgKey, 
				UserID					= s.UserID,
				PaymentMode				= s.PaymentMode,
				OrderDate				= s.OrderDate,
				OrderDetail				= s.OrderDetail,
				UseDeliveryAddress		= s.UseDeliveryAddress,
				WriteToLiquiDate		= s.WriteToLiquiDate,
				WriteInvoiceToBvDate	= s.WriteInvoiceToBvDate,
				WriteInvoiceToVrspDate	= s.WriteInvoiceToVrspDate
		;

	
		if coalesce(@CashSaleID,0) = 0
		begin
			set @CashSaleID = @@IDENTITY
		end

		;with selDetails
		as
		(
			select	@CashSaleID		'CashSaleID',
					d.DetailsID,
					d.Price,
					d.ProductID,
					d.Quantity,
					d.RabattFix,
					d.RabattPercent1,
					d.RabattPercent2,
					d.TaxCode,
					d.TaxRate
			from	@Details d
		)
		merge into CashSale.datCashSaleDetails t
		using selDetails s on
			s.CashSaleID = t.CashSaleID
			and s.[DetailsID] = t.[DetailsID]
		when not matched by source
		and t.CashSaleID = @CashSaleID
		then 
			delete
		when not matched by target then
		insert (
			CashSaleID,
			ProductID,
			Quantity,
			Price,
			RabattPercent1,
			RabattPercent2,
			RabattFix,
			TaxCode,
			TaxRate
				)
		values	(
			s.CashSaleID,
			s.ProductID,
			s.Quantity,
			s.Price,
			s.RabattPercent1,
			s.RabattPercent2,
			s.RabattFix,
			s.TaxCode,
			s.TaxRate
				)
		when matched then
		update 
			set
				CashSaleID		= s.CashSaleID,
				ProductID		= s.ProductID,
				Quantity		= s.Quantity,
				Price			= s.Price,
				RabattPercent1	= s.RabattPercent1,
				RabattPercent2	= s.RabattPercent2,
				RabattFix		= s.RabattFix,
				TaxCode			= s.TaxCode,
				TaxRate			= s.TaxRate;
		
		

		;with jobDetails
		as
		(
			select	d.JobID,
					d.UserID,
					d.JobType,
					d.Title,
					d.Einheit,
					d.Fahrzeug,
					d.Datum,
					d.StartTime,
					d.EndTime,
					d.Leistung,
					d.LeistungProzent,
					d.LeistungZeit,
					d.ProjektName,
					d.[Status],
					@CashSaleID 'CashSaleID',
					d.ProjectID,
					d.Area,
					d.SubArea,
					d.Sb,
					d.Ss,
					d.Datei,
					d.Fa,
					d.Vr
			from	@JobDetails d
		)
		merge into Job.datJob t
		using jobDetails s on
			s.JobID = t.JobID
		when not matched then
		insert (
				JobID,
				UserID,
				JobType,
				Title,
				Einheit,
				Fahrzeug,
				Datum,
				StartTime,
				EndTime,
				Leistung,
				LeistungProzent,
				LeistungZeit,
				ProjektName,
				[Status],
				CashSaleID,
				ProjectID,
				Area,
				SubArea,
				Sb,
				Ss,
				Datei,
				Fa,
				Vr
				)
		values	(
				s.JobID,
				s.UserID,
				s.JobType,
				s.Title,
				s.Einheit,
				s.Fahrzeug,
				s.Datum,
				s.StartTime,
				s.EndTime,
				s.Leistung,
				s.LeistungProzent,
				s.LeistungZeit,
				s.ProjektName,
				s.[Status],
				s.CashSaleID,
				s.ProjectID,
				s.Area,
				s.SubArea,
				s.Sb,
				s.Ss,
				s.Datei,
				s.Fa,
				s.Vr
				)
		when not matched by source 
		and t.CashSaleID = @CashSaleID
		then
			delete
		when matched then
		update
			set
				UserID			= s.UserID,
				JobType			= s.JobType,
				Title			= s.Title,
				Einheit			= s.Einheit,
				Fahrzeug		= s.Fahrzeug,
				Datum			= s.Datum,
				StartTime		= s.StartTime,
				EndTime			= s.EndTime,
				Leistung		= s.Leistung,
				LeistungProzent	= s.LeistungProzent,
				LeistungZeit	= s.LeistungZeit,
				ProjektName		= s.ProjektName,
				[Status]		= s.[Status],
				CashSaleID		= s.CashSaleID,
				ProjectID		= s.ProjectID,
				Area			= s.Area,
				SubArea			= s.SubArea,
				Sb				= s.Sb,
				Ss				= s.Ss,
				Datei			= s.Datei,
				Fa				= s.Fa,
				Vr				= s.Vr
				;


		if @PaymentMode = @saveDeliveryAddress and @UseDeliveryAddress <> 0
		begin
			;with selDeliver
			as
			(
				select	@CashSaleID		'CashSaleID',
						@DeliverName	'DeliverName', 
						@DeliverName2	'DeliverName2', 
						@DeliverOrt		'DeliverOrt', 
						@DeliverPlz		'DeliverPlz', 
						@DeliverStrasse	'DeliverStrasse'
			)
			merge into [CashSale].[datDeliveryAddresses] t
			using SelDeliver s on
				s.CashSaleID = t.CashSaleID
			when not matched then
			insert	(
					CashSaleID,
					Name, 
					Name2, 
					Ort, 
					Plz, 
					Strasse
					)
			values	(
					s.CashSaleID,
					s.DeliverName, 
					s.DeliverName2, 
					s.DeliverOrt, 
					s.DeliverPlz, 
					s.DeliverStrasse
					)
			when matched then
			update
				set
					CashSaleID	= s.CashSaleID,
					Name		= s.DeliverName, 
					Name2		= s.DeliverName2, 
					Ort			= s.DeliverOrt, 
					Plz			= s.DeliverPlz, 
					Strasse		= s.DeliverStrasse;

		end
		else begin
			delete	CashSale.datDeliveryAddresses
			where	CashSaleID = @CashSaleID
		end

	end try
	begin catch
		Select @CashSaleID 'CashSaleID', -1 'Return', ERROR_MESSAGE() 'Error' 
		goto WithError
	end catch

	select @CashSaleID 'CashSaleID', 0 'Return', null 'Error'

	commit tran
	
	return 0

WithError:
	if @@TRANCOUNT > 0
	begin
		set @logText = 'SaveCashSale: ' + cast(@InvoiceNo as char(10))
		exec @rc = dbo.SaveLog
			@UserID = @UserID,
			@LogType = 'S',
			@LogArea = 'Bv',
			@LogDetails = @logText,
			@IsError = 1
		rollback tran
	end
	return -1

end


GO


